package model;

import globals.SystemGeneralState;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import javax.swing.JOptionPane;

import controller.GlobalOperations;

public class PersonDAO {

	private List<Person> people;
	private Connection con = null;
	private DatabaseCommons dbc = new DatabaseCommons();
	private int maxId;
	private GlobalOperations gOps;

	public PersonDAO() {
		/*
		 * 2013.03.11 - changing ArrayList to LinkedList since the latter is
		 * optimized for addition or subtraction of items
		 */
		people = new LinkedList<Person>();
		gOps = new GlobalOperations();
	}

	public PersonDAO(LinkedList<Person> people) {
		super();
		this.people = people;
	}

	public void addPerson(Person p) {
		people.add(p);
		/*
		 * System.out.println(
		 * "addPerson succeeded!\n*************Person added is:\n" +
		 * "idPerson: " + p.getId() + ", name: " + p.getName() + ", surname: " +
		 * p.getSurname() + ", Collateral client: " + p.getCc() + ", CC desc: "
		 * + p.getColClient() + ", isChkIC? = " + p.isChkIndirectClient());
		 */
	}

	public void remPerson(int index) {
		people.remove(index);
	}

	public List<Person> getElements() {
		// Collections.unmodifiableList prevents other classes from modifying
		// the list by the reference retrieved
		return Collections.unmodifiableList(people);
	}

	/*
	 * command to save table rows to a file
	 */
	public void saveToFile(File file) throws IOException {
		// fos is needed to open an output stream
		FileOutputStream fos = new FileOutputStream(file);
		// oos is the class that can write the stream
		ObjectOutputStream oos = new ObjectOutputStream(fos);

		/*
		 * I create an array of the people list, which may seem redundant but
		 * the oos' method use arrays, not lists.
		 */
		Object[] peopleArray = people.toArray(new Object[people.size()]);

		oos.writeObject(peopleArray);

		// I finally close the stream
		oos.close();
	}

	public void loadFromFile(File file) throws IOException,
			ClassNotFoundException {
		FileInputStream fis = new FileInputStream(file);
		ObjectInputStream ois = new ObjectInputStream(fis);

		// I get the info from the db to the peopleArray
		Person[] peopleArray = (Person[]) ois.readObject();

		// I first clear the peole ArrayList
		people.clear();
		// Then, I set it with the new value of peopleArray and transform it to
		// a List object
		people.addAll(Arrays.asList(peopleArray));

		// I finally close the stream
		ois.close();
	}

//	public void h2Connect() throws SQLException {
//		// checks the mysql-connector
//		try {
//			Class.forName("org.h2.Driver");
//		} catch (ClassNotFoundException e) {
//			System.out
//					.println("ClassNotFoundException while seeking the H2 driver: "
//							+ e);
//		}
//		// create connection
//		String url = "jdbc:h2:~/test", sqlUname = "sa", sqlPass = "";
//		// changing url from embedded to server mode
//		url = "jdbc:h2:tcp://localhost/~/test";
//		// sqlPass = "r00t";
//
//		// if connection is already created, just return
//		if (con != null)
//			return;
//		// otherwise, create it
//		con = DriverManager.getConnection(url, sqlUname, sqlPass);
//		System.out.println("Connected: " + con);
//	}
//
//	public void disconnect() throws SQLException {
//		System.out.println("...db connection closing now.");
//		if (con != null) {
//			con.close();
//			System.out.println("isClosed? " + con.isClosed());
//		}
//	}

	/*
	 * Method to insert and or update a list of people
	 */
	public void saveToDB() throws SQLException {
		String csql, isql, usql, pname, psurname;
		int col = 0, colClient = 0;
		boolean isChkIndirectClient;
		/*
		 * id will be used to check the id's while I go through the elements
		 * reps to compare occurrences
		 */
		int id = -1, reps = -1;
		/*
		 * sql queries with question marks are used in prepared statements since
		 * they are more secure due to being more difficult to be attacked by a
		 * code injection tactic.
		 */
		csql = "select count(*) as reps from People where id_person = ?";
		isql = "insert into People (id_person, name, surname, collateral_client, is_direct_client) values(?, ?, ?, ?, ?)";
		usql = "update People set name = ?, surname = ?, collateral_client = ?, is_direct_client = ? where id_person = ?";
		PreparedStatement cps = null, eps = null;
		ResultSet rs = null;
		
		
		/*
		 * trying to factor connection methods 
		 * */
		dbc.h2Connect();
		con = dbc.getConnection();
		
		cps = con.prepareStatement(csql);

		for (Person p : people) {
			id = p.getId();
			pname = p.getName();
			psurname = p.getSurname();
			colClient = p.getIdCollateralClient();
			isChkIndirectClient = p.isChkIndirectClient();

			System.out.println("values created for insertion - id: " + id
					+ ", pname: " + pname + ", psurname: " + psurname
					+ ", colClient: " + colClient + ", isChkIndirectClient: "
					+ isChkIndirectClient);
			// first parameter is the index of the wildcard, this once there's
			// just one though
			cps.setInt(1, id);
			// execute the query
			rs = cps.executeQuery();
			// go to cursor in the first position
			rs.next();
			// get the count
			reps = rs.getInt("reps");

			if (reps == 0) {
				// preparing the insert with the execution prepared statement
				eps = con.prepareStatement(isql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setInt(col++, id);
				eps.setString(col++, pname);
				eps.setString(col++, psurname);
				eps.setInt(col++, colClient);
				eps.setBoolean(col++, isChkIndirectClient);

				System.out.println("Inserting person with id: " + id + " ("
						+ pname + " " + psurname + ", collateral_client: "
						+ colClient + ", isChkIndirectClient: "
						+ isChkIndirectClient + ").");
				eps.executeUpdate();
			} else {
				// preparing the update with the execution prepared statement
				eps = con.prepareStatement(usql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setString(col++, pname);
				eps.setString(col++, psurname);
				eps.setInt(col++, colClient);
				eps.setBoolean(col++, isChkIndirectClient);
				eps.setInt(col++, id);

				System.out.println("Updating person with id: " + id + " ("
						+ pname + " " + psurname + ", collateral_client: "
						+ colClient + ", isChkIndirectClient: "
						+ isChkIndirectClient + ").");
				eps.executeUpdate();
			}
		}
		csql = "cleaned from saveToDB()";
		isql = "cleaned from saveToDB()";
		usql = "cleaned from saveToDB()";
		cps.close();
		eps.close();
		
		/*
		 * trying to factor connection methods 
		 * */
		dbc.disconnect();
	}

	public void loadFromDB() throws SQLException {
		String csql, isql, usql, pname, psurname;
		int col = 0;
		/*
		 * very very first thing, I'll declare my prepared statement
		 */
		csql = "select id_person, name, surname, collateral_client, is_direct_client from people order by name";
		
		/*
		 * trying to factor connection methods 
		 * */
//		dbc.h2Connect();
		con = dbc.getConnection();
		
		Statement select = con.createStatement();
		// first thing, I'll clear the list
		people.clear();

		// I execute the query and load it in the rs resultset
		ResultSet rs = select.executeQuery(csql);

		while (rs.next()) {
			/*
			 * System.out.println("id: " + rs.getInt("id") + ", name: " +
			 * rs.getString("name") + ", surname: " + rs.getString("surname"));
			 */

			/*
			 * people.add(new Person(rs.getInt("id"), rs.getString("name"), rs
			 * .getString("surname"), CollateralClient.valueOf(rs
			 * .getString("collateral_client")), rs
			 * .getBoolean("isDirectClient")));
			 */
			Person tmp = new Person(rs.getInt("id_person"),
					rs.getString("name"), rs.getString("surname"),
					CollateralClient.valueOf("noCollateral"),
					rs.getBoolean("is_direct_client"));
			//System.out.println("getting person: " + tmp);
			people.add(tmp);
		}

		/*
		 * finally, I close the resultset and the statement
		 */
		csql = "cleaned from loadFromDB()";
		select.close();
		rs.close();
		
		/*
		 * trying to factor connection methods 
		 * */
//		dbc.disconnect();

	}

	
	public int getMaxId() throws SQLException {
		String csql, isql, usql, pname, psurname;
		int col = 0;
		/*
		 * getting max id
		 */
		csql = "select max(id_person) as max_id from people";
		
		/*
		 * trying to factor connection methods 
		 * */
		dbc.h2Connect();
		con = dbc.getConnection();
		
		Statement select = con.createStatement();
		// first thing, I'll clear the list
		people.clear();

		// I execute the query and load it in the rs resultset
		ResultSet rs = select.executeQuery(csql);

		while (rs.next()) {
			maxId = rs.getInt("max_id");
//			System.out.println("PersonDAO.getMaxId: rs(maxId): " + maxId);
		}

		/*
		 * finally, I close the resultset and the statement
		 */
		csql = "cleaned from loadFromDB()";
		select.close();
		rs.close();
		
		/*
		 * trying to factor connection methods 
		 * */
		dbc.disconnect();
		
		return maxId;
	}
	
//	public void addEmptyPerson() {
//		try {
//			int lastId = addPersonNoCommit();
//			sgs = SystemGeneralState.getInstance();
//			sgs.put("lastPersonInserted", String.valueOf(lastId));
//
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//	}
	
//	public void setGlobalString(String key, int value) {
//		sgs = SystemGeneralState.getInstance();
//		sgs.put(key, String.valueOf(value));
//	}
	
	public int addEmptyPerson() throws SQLException {
		String isql;
		int col = 0, colClient = 0;
		/*
		 * id will be used to check the id's while I go through the elements
		 * reps to compare occurrences
		 */
		int id = -1, reps = -1, lastId = -1;
		/*
		 * sql queries with question marks are used in prepared statements since
		 * they are more secure due to being more difficult to be attacked by a
		 * code injection tactic.
		 */
		isql = "insert into People (name, surname, collateral_client, is_direct_client) values(?, ?, ?, ?)";
		PreparedStatement eps = null;
		ResultSet rs = null;
		
		
		/*
		 * trying to factor connection methods 
		 * */
		//dbc.h2Connect();
		con = dbc.getConnection();
		
		eps = con.prepareStatement(isql);
		eps.setString(1, "");
		eps.setString(2, "");
		eps.setInt(3, -1);
		eps.setBoolean(4, false);
		
		eps.executeUpdate();
		
		//	method that gets the last inserted index from the prepared statement
		lastId = dbc.getLastInsertedIndex(eps, "addEmptyPerson()");
		
		// sets the global value for last person inserted
		gOps.setGlobal("lastPersonInserted", lastId);
		
//		System.out.println("last generated id: " + lastId);
		
		isql = "cleaned from saveToDB()";
		eps.close();
		
		/*
		 * trying to factor connection methods 
		 * */
//		dbc.disconnect();
		
		return lastId;
	}
	
	
	
//	public void addTelephoneForPerson(int idTelephoneType, int idPerson, String telephone) {
//		// SET AUTOCOMMIT FALSE;
//
//		try {
////			dbc.h2Connect();
//			//dbc.setAutoCommit(false, con);
//			int lastId = addTelephoneNoCommit(idTelephoneType, idPerson, telephone);
////			sgs = SystemGeneralState.getInstance();
////			sgs.put("lastTelephoneInserted_" + lastId, String.valueOf(lastId));
//
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//	}
	
	

}
